# coding:utf-8
import traceback

from django.db import OperationalError, InternalError
from WebBase.settings import logger

from tools.DbConnection import getConn,reConn


def test_conn():
    """
        测试连接池连接是否正常
        return：
        res：True：正常，False：不正常
        msg：如果不正常，为异常信息
    """

    test_sql = """select sysdate from dual"""

    conn = None
    cur = None
    res = False
    msg = ""
    try:
        conn = getConn()
        cur = conn.cursor()
        cur.execute(test_sql)
        msg = cur.fetchall()[0][0]
        res = True
    except Exception,e:
        traceback.print_exc()
        msg = e
    finally:
        if cur:
            cur.close()
        if conn:
            conn.close()
        return res,msg


def call_reConn():
    """
        重新创建连接池
    """
    reConn()

def executeQueryList(sql):
    """
        dbutils 数据连接池
            只能执行数据查询sql语句,否则会抛错
        @parm: 要执行的sql语句
        @return:
            []:查询结果为空
            None:sql语句执行失败,出现异常
                    二维list:正常结果
    """

    conn = None
    cur = None
    res = None
    try:
        conn = getConn()
        cur = conn.cursor()
        cur.execute(sql)
        res = cur.fetchall()
    except (OperationalError, InternalError):
        call_reConn()
        traceback.print_exc()
    except:
        traceback.exc()
    finally:
        if cur:
            cur.close()
        if conn:
            conn.close()
        return res

def executeQuery_one(sql):
    """
        dbutils 数据连接池
                只能执行数据查询sql语句，否则会报错
                执行sql查询语句，获取第一条记录
        @parm：要执行的sql语句
        @return：
            []:查询结果为空
            None:sql语句执行失败,出现异常
            list:正常结果
    """

    conn = None
    cur = None
    res = None
    try:
        conn = getConn()
        cur = conn.cursor()
        cur.execute(sql)
        res = cur.fetchone()
    except (OperationalError,InternalError):
        call_reConn()
    except:
        traceback.print_exc()
    finally:
        if cur:
            cur.close()
        if conn:
            conn.close()
        return res


def executeQueryMap(sql):

    logger.info("executeQueryMap:" + sql)
    try:
        conn = getConn()
        cur = conn.cursor()
        cur.execute(sql)
        res = cur.fetchall()
        if res != None:
            title = [str(row[0]).lower() for row in cur.description]
            colMap = [dict(zip(title, [str(col) for col in row])) for row in res]
    except Exception, e:
        logger.info(e)

    return colMap

def executeUpdate(sql):
    """
        dbutils 数据连接池
                执行数据操作语句，包括 update,insert,delete
        @parm:要执行的sql
        @return:
            None:sql语句执行失败,出现异常
            number:影响记录条数
            -2:数据库连接失败导致执行失败
    """
    conn = None
    cur = None
    res = None
    try:
        conn = getConn()
        cur = conn.cursor()
        cur.execute(sql)
        res = cur._cursor.rowcount
        conn.commit()
    except Exception,e:
        if conn:
            conn.rollback()
        traceback.print_exc()
    finally:
        if res == -1:#可能是数据库断开连接
            ret,msg = test_conn()
            if not ret:
                call_reConn()
                res = -2
        if cur:
            cur.close()
        if conn:
            conn.close()
        return res



def muti_executeUpdate(sql_list):
    """
        dbutils 数据连接池
                执行多条数据操作语句,可以用于多条sql语句的事务性操作,包括 update,insert,delete

        @parm:要执行的sql语句[]
        @return:
            (flag,res):
                flag<Ture or False>:批次是否全部执行成功
                res<list>:每天sql语句执行影响的行数,如果执行失败,由此可以判断第几条sql语句执行失败
                            如果遇到数据库断开的情况,返回[-2,]
    """

    conn = None
    cur = None
    res = []
    flag = True

    try:
        conn = getConn()
        cur = conn.cursor()
        for sql in sql_list:
            cur.execute(sql)
            num = cur._cursor.rowcount
            res.append(num)
        conn.commit()
    except Exception,e:
        if conn:
            conn.rollback()
        traceback.print_exc()
    finally:
        if -1 in res:
            ret,msg = test_conn()
            if not ret:
                call_reConn()
                flag = False
                res = [-2,]
        if cur:
            cur.close()
        if conn:
            conn.close()
        return flag,res

# // //获取翻页sql
def getPageSql(sql, page, rows):
    # // 每页的开始记录 第一页为1 第二页为number +1
    start = (page - 1) * rows 
    end = page * rows 

    buffer = str()
    buffer += "SELECT * FROM ( SELECT ROWNUM AS NUMROW, tttt.* from ("
    buffer +=  sql
    buffer += ") tttt) WHERE NUMROW > " + str(start) + " AND NUMROW <=" + str(end) + "  "

    return buffer

if __name__ == '__main__':
    # print test_conn()

    # 测试 executeQuery
    sql = "select t.is_lock from Sm_Usermessage t  where t.login_name = '" + "admin" + "'"
    ret = executeQueryList(sql)
    print ret
    print sql
    sql = r"select t.is_lock from Sm_Usermessage t  where t.login_name = '%s'" % ("admin")
    ret = executeQueryList(sql)
    print ret
    print sql


    # # 测试 executeQuery_one
    # sql = "select * from cgk t"
    # ret = executeQuery_one(sql)
    # print ret[1]
    # print ret
    #
    # # 测试 executeUpdate
    #
    # sql = "INSERT INTO cgk VALUES(cgk_seq.nextval, u'商海通牒', sysdate)"
    # print executeUpdate(sql)
    #
    # sql = "UPDATE cgk t SET t.title = 'ss' WHERE t.id = 1"
    # print executeUpdate(sql)
    #
    # sql = "DELETE cgk t WHERE t.id = 1"
    # print executeUpdate(sql)
    #
    # # 测试 muti_executeUpdate
    # sql_list = []
    # sql_list.append("INSERT INTO cgk VALUES(cgk_seq.nextval, u'商海通牒')")
    # sql_list.append("UPDATE cgk t SET t.title = 'ss' WHERE t.id = 1")
    # sql_list.append("DELETE cgk t WHERE t.id = 1")
    # print muti_executeUpdate(sql_list)


